Database Changes 4.43
This section contains details of changes between release 42.0.1078 and 43.0.1003.
Table Changes
New Tables
| Table Name | Description |
|---|---|
| HESA_OFF_VENUE_ACTIVITIES | The details of a student placement activity or time spent abroad. |
| ILP_DEF_INSTANCE_PERSON_FNS | Links person functions for assigning staff by register to an assigned ILP. |
| ILP_TARGET_LINKS | Links learner ILPs and courses to custom staff created targets for learners. |
New Columns Added to Existing Tables
| Table Name | Column Name | Type (Size) | Nullable | Description |
|---|---|---|---|---|
| FILTER_DEFINITIONS | LICENCE | nvarchar(20) | Y | The licence required to use the filter definition. |
| ILP_COMMENTS | CAN_LEARNER_VIEW | nvarchar(1) | N | Specifies whether a learner can view the comment (Y/N) [DEFAULT=Y]. |
| ILP_COMMENTS | STATUS | nvarchar(40) | N | Specifies the status of the comment (OPEN/REMOVED) [DEFAULT=OPEN]. |
| ILP_DEFINITION_INSTANCES | PERSON_FUNCTION_MODE | nvarchar(10) | N | Specifies whether staff assigned by register should use all person functions or a specified list (ALL/SPECIFIED) [DEFAULT=ALL]. |
| ILP_QUESTION_DEFINITIONS | ALLOW_COMMENTS | nvarchar(1) |
N |
Specifies whether the comments will be allowed for this question (Y/N) [DEFAULT=Y]. |
| ILP_QUESTION_SNAPSHOTS | ALLOW_COMMENTS | nvarchar(1) | N | Specifies whether the comments will be allowed for this question (Y/N) [DEFAULT=Y]. |
| SFG_RECORD_INBOX_LOGS | EMAIL_ADDRESS | nvarchar(120) | Y | The email address of the recipient. |
| SFG_RECORD_INBOX_LOGS | SFG_RECORD_DEFINITION_ID | numeric(10, 0) | Y | The identifier of the associated safeguarding record definition [FK=SFG_RECORD_DEFINITIONS.ID]. |
| SFG_RECORD_INBOX_LOGS | OOH_LEARNER_CODE | numeric(10, 0) | Y | The associated learner code of out of hours email. |
| SFG_RECORD_TEMPLATES | EMAIL_ADDRESS | nvarchar(120) | Y | The email address of the recipient. |
| T_EST_ATTENDANCEBENCHMARK | UPDATED_BY | nvarchar(30) | Y | The person who last updated this record. |
| T_EST_ATTENDANCEBENCHMARK | UPDATED_DATE | datetime | Y | The date this record was last updated. |
| UCAS_APPLICANT_CHOICES | ACTION | nvarchar(1) | Y | R - RBD, D - DBD, U - Uprated, A - Auto Decline. |
Changes to Existing Columns
| Table Name | Column Name | Type (Size) | Nullable | Previous Value Types (ize) [Nullable] |
|---|---|---|---|---|
| PEOPLE_HESA | SID | nvarchar(17) | Y | numeric(17, 0) [Y] |
| SFG_RECORD_INBOX_LOGS | SFG_RECORD_INBOX_ID | numeric(10, 0) | Y | numeric(10, 0) [N] |
| UCAS_APPLICANT_DETAILS | COUNTRYCODE | nvarchar(40) | Y | nvarchar(3) [Y] |
| UCAS_APPLICANT_DETAILS | COUNTRYOFBIRTHCODE | nvarchar(40) | Y | nvarchar(3) [Y] |
| UCAS_APPLICANT_DETAILS | DUALNATIONALITYCODE | nvarchar(50) | Y | nvarchar(3) [Y] |
| UCAS_APPLICANT_DETAILS | GCE | nvarchar(3) | Y | nvarchar(2) [Y] |
| UCAS_APPLICANT_DETAILS | HOMECOUNTRYCODE | nvarchar(40) | Y | nvarchar(3) [Y] |
| UCAS_APPLICANT_DETAILS | STUDENTSUPPORTARRANGEMENTS | nvarchar(50) | Y | nvarchar(30) [Y] |
View Changes
New Views
The following views have been added:
-
EBS_DOC_TYPES_LEARNER
-
EBS_HESA_OFF_VENUE_ACTIVITIES
-
EBS_ILP_COHORT_LEARNER_CDR
-
EBS_ILP_COHORT_LEARNER_LLWR
-
EBS_STUDYLINK_REFUNDS_NOFILE
-
EBS_UCAS_ADDRESSES
-
EBS_UCAS_AUTOMATCH
-
EBS_UCAS_VERIFIERS
-
EBS_VERIFIER_ATTENDANCE_BENCHMARK
New Columns in Existing Views
| View Name | Column Name |
|---|---|
| EBS_BOOKABLE_INTERVIEWS | USER_1 |
| EBS_BOOKABLE_INTERVIEWS | USER_2 |
| EBS_BOOKABLE_INTERVIEWS | USER_3 |
| EBS_BOOKABLE_INTERVIEWS | USER_4 |
| EBS_BOOKABLE_INTERVIEWS | USER_5 |
| EBS_BOOKABLE_INTERVIEWS | USER_6 |
| EBS_BULK_ENROLMENT | APEL |
| EBS_BULK_ENROLMENT | COLPROVTYPEID |
| EBS_BULK_ENROLMENT | CONTINUING |
| EBS_BULK_ENROLMENT | EMPFEES |
| EBS_BULK_ENROLMENT | EMPLOYINGSCHOOL |
| EBS_BULK_ENROLMENT | EMPLOYINGSCHOOL |
| EBS_BULK_ENROLMENT | FEEELIG |
| EBS_BULK_ENROLMENT | FEEMETHOD |
| EBS_BULK_ENROLMENT | FEESTATUS |
| EBS_BULK_ENROLMENT | FUNDCODE |
| EBS_BULK_ENROLMENT | FUNDING_BODIES |
| EBS_BULK_ENROLMENT | INACTIVEMOD |
| EBS_BULK_ENROLMENT | INTENDEDTHESISTITLE |
| EBS_BULK_ENROLMENT | INTERCALATION |
| EBS_BULK_ENROLMENT | LEADSCHOOL |
| EBS_BULK_ENROLMENT | LEARNER_FTE_COM |
| EBS_BULK_ENROLMENT | MODCOUNT |
| EBS_BULK_ENROLMENT | MODULEOUTCOME |
| EBS_BULK_ENROLMENT | MODULERESULT |
| EBS_BULK_ENROLMENT | NHSEMP |
| EBS_BULK_ENROLMENT | PARTNERNUMHUS |
| EBS_BULK_ENROLMENT | PARTNERSID |
| EBS_BULK_ENROLMENT | PARTNERUKPRN |
| EBS_BULK_ENROLMENT | PGRLANGID |
| EBS_BULK_ENROLMENT | PGRLANGPCNT |
| EBS_BULK_ENROLMENT | PHDSUB |
| EBS_BULK_ENROLMENT | PL_CODE |
| EBS_BULK_ENROLMENT | PLACEMENT |
| EBS_BULK_ENROLMENT | PREPFLAG |
| EBS_BULK_ENROLMENT | PUS_HESA_ID |
| EBS_BULK_ENROLMENT | PUS_ILRHE_ID |
| EBS_BULK_ENROLMENT | QTS |
| EBS_BULK_ENROLMENT | RCSTDID |
| EBS_BULK_ENROLMENT | RCSTDNT |
| EBS_BULK_ENROLMENT | RSNSCSEND |
| EBS_BULK_ENROLMENT | THESISTITLE |
| EBS_BULK_ENROLMENT | TRN |
| EBS_BULK_ENROLMENT | YEARPRG |
| EBS_CDRSTATISTICSDATA | PARENT_ID |
| EBS_CDRSTATISTICSDATA | QUAL_AIM |
| EBS_CDRSTATISTICSDATA | QUAL_HOURS |
| EBS_CORRESPONDENCE_STUDENTS | USE_SMS |
| EBS_ILP_COHORT_LEARNER_UIO | FES_SOURCE_FINANCE |
| EBS_ILP_COHORT_LEARNER_UIO | MSTUFEE |
| EBS_UCAS_APPLICANT | EBS_SEX |
| EBS_UCAS_APPLICANT_CHOICES | ACTION |
| EBS_UCAS_APPLICANT_CHOICES | CAMPUS |
| EBS_UCAS_APPLICANT_CHOICES | EXISTING_PROGRESS_CODE |
| EBS_UCAS_APPLICANT_CHOICES | PAYLOAD_PROGRESS_CODE |
| EBS_UCAS_APPLICANT_CHOICES | REPLY |
| EBS_UCAS_APPLICANT_CHOICES | UPDATE_APPLICATION |
| EBS_UCAS_ATTAINMENTS | ATTAINMENT_CODE |
| EBS_UCAS_ATTAINMENTS | PERSON_CODE |
Updates for SP2
Table Changes
New Tables
| Table Name | Description |
|---|---|
| PEOPLE_DSR | PEOPLE extension table for data system refresh (DSR) fields. |
| UI_DSR | UNIT_INSTANCE extension table for data system refresh (DSR) fields. |
| PEOPLE_UNITS_DSR | PEOPLE_UNITS extension table for data system refresh (DSR) fields. |
New Columns Added to Existing Tables
| Table Name | Column Name | Type (Size) | Nullable | Description |
|---|---|---|---|---|
| SDR_STUDENTS_SNAPSHOT | DISAB_1 | nvarchar(40) | Y | Disability Support Need 1 |
| SDR_STUDENTS_SNAPSHOT | DISAB_2 | nvarchar(40) | Y | Disability Support Need 2 |
| SDR_STUDENTS_SNAPSHOT | DISAB_3 | nvarchar(40) | Y | Disability Support Need 3 |
| SDR_STUDENTS_SNAPSHOT | DISAB_4 | nvarchar(40) | Y | Disability Support Need 4 |
| SDR_STUDENTS_SNAPSHOT | DISAB_5 | nvarchar(40) | Y | Disability Support Need 5 |
| SDR_STUDENTS_SNAPSHOT | DISAB_6 | nvarchar(40) | Y | Disability Support Need 6 |
| SDR_STUDENTS_SNAPSHOT | DISAB_7 | nvarchar(40) | Y | Disability Support Need 7 |
| SDR_STUDENTS_SNAPSHOT | IWI_4 | nvarchar(12) | Y | Indicates the IWI affiliation of a learner |
| SDR_STUDENTS_SNAPSHOT | IWI_5 | nvarchar(12) | Y | Indicates the IWI affiliation of a learner |
| SDR_STUDENTS_SNAPSHOT | IWI_6 | nvarchar(12) | Y | Indicates the IWI affiliation of a learner |
| SDR_STUDENTS_SNAPSHOT | ETHNIC_4 | nvarchar(9) | Y | Indicates the ethnicity of a learner |
| SDR_STUDENTS_SNAPSHOT | ETHNIC_5 | nvarchar(9) | Y | Indicates the ethnicity of a learner |
| SDR_STUDENTS_SNAPSHOT | ETHNIC_6 | nvarchar(9) | Y | Indicates the ethnicity of a learner |
| SDR_STUDENTS_SNAPSHOT | DISABILITY_STATUS | numeric(1) | N | Indicates the disability status of a learner |
| SDR_COURSE_REGISTER_SNAPSHOT | DISC_COURSE_TUITION_FEE | numeric(8,2) | Y | The discounted course tuition fee charged to domestic learners who enrol in the course. |
| SDR_COURSE_REGISTER_SNAPSHOT | DISC_COMPULSORY_CRS_COST_FEE | numeric(8,2) | Y | The discounted course fee charged to domestic learners who enrol in the course. |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | CONSORTIUM | nvarchar(6) | Y | Identifies the cooperative arrangement among groups or institutions. |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | ITE_SECTOR | nvarchar(1) | N | Identifies the sector for a learner enrolled in Initial Teacher Education |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | ITE_SUBJECT1 | nvarchar(4) | Y | The specific curriculum subject areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary. |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | ITE_SUBJECT2 | nvarchar(4) | Y | The specific curriculum subject areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary. |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | ITE_SUBJECT3 | nvarchar(4) | Y | The specific curriculum subject areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary. |
| SDR_COURSE_ENROLMENTS_SNAPSHOT | ITE_SUBJECT4 | nvarchar(4) | Y | The specific curriculum subject areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary. |
Updates for SP3
Table Changes
New Columns in Existing Tables
| Table Name | Column Name | Type (Size) | Nullable | Description |
|---|---|---|---|---|
| PEOPLE_UNITS | DSR_COMPLETION_RETURN_STATUS | nvarchar(1) | Y | Indicates the status of the DSR return (P=Pending, E=Extracted, S=Submitted) |
| ATTAINMENTS | DSR_COMPLETION_RETURN_STATUS | nvarchar(1) | Y | Indicates the status of the DSR return (P=Pending, E=Extracted, S=Submitted) |
| SDR_SNAPSHOT | DSR_STATUS | nvarchar(1) | Y | Indicates the status of the DSR return (P=Pending, E=Extracted, S=Submitted) |
Changes to Existing Columns
| Table Name | Column Name | Type (Size) | Nullable | Previous Value Type (size) [Nullable] |
|---|---|---|---|---|
| PEOPLE_DSR | DISABILITY_STATUS | nvarchar(1) | Y | nvarchar(1) [N] |
| SDR_SNAPSHOT | SDR_STATUS | nvarchar(1) | Y | nvarchar(1) [N] |